package dao;

import entity.*;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import relationEntity.MemberUser;
import relationEntity.UserMember;

import java.util.List;

/**
 * Created by jifeng on 2016/12/2 0002.
 */
public class UserDao {

    private SessionFactory sessionFactory;

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }
    public SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    private Session getCurrentSession() {
      return sessionFactory.getCurrentSession();
  }

    public void addUser(User user) {
        getCurrentSession().save(user);
    }
    public void deleteUser(int id) {
       List <Users> users = getUsers(id);
        if (users != null) {
            getCurrentSession().delete(users);
        }
    }
    private List<Users> getUsers(int id) {
        return getCurrentSession().createQuery("from t_user where id ="+id+"").list();
    }

    public List<UserMember> findUserList(int offset,String date,String source,String level,String userName, int length){
        List<UserMember> userMemberList = null;
        try {
            String sql="select u.id,u.userName,u.startDate,m.level,u.name,u.gender,u.phone,u.email,u.orderNum,u.source ";
            String exceptSelectSql="from User u\n" +
                    "LEFT JOIN member AS m ON m.userId = u.id\n" +
                    "WHERE u.deletes = 0";
            if (level !=null & level!=""){
                exceptSelectSql+=" AND m.level='"+level+"'";
            }
            if (source != null & source != ""){
                exceptSelectSql+=" AND u.source ='"+source+"'";
            }
            if (date !=null & date!="") {
                if (Integer.valueOf(date) == 1) {
                    exceptSelectSql += " ORDER BY u.startDate";

                } else if (Integer.valueOf(date) == 0) {
                    exceptSelectSql += " ORDER BY u.startDate DESC ";

                }
            }
            if (userName!=null & userName!=""){
                exceptSelectSql+=" AND u.name LIKE '%"+userName+"%'";
            }
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id", StandardBasicTypes.INTEGER);
            query.addScalar("userName", StandardBasicTypes.STRING);
            query.addScalar("startDate", StandardBasicTypes.DATE);
            query.addScalar("level", StandardBasicTypes.STRING);
            query.addScalar("name", StandardBasicTypes.STRING);
            query.addScalar("gender", StandardBasicTypes.STRING);
            query.addScalar("phone", StandardBasicTypes.STRING);
            query.addScalar("email", StandardBasicTypes.STRING);
            query.addScalar("orderNum", StandardBasicTypes.LONG);
            query.addScalar("source", StandardBasicTypes.STRING);
            query.setResultTransformer(Transformers.aliasToBean(UserMember.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            userMemberList =query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return userMemberList;
    }


    public List<Role> findRoleList(int offset, int length){
        List<Role> roleList = null;
        try {
            String sql="SELECT * FROM role";
            SQLQuery query = getCurrentSession().createSQLQuery(sql);
            query.setResultTransformer(Transformers.aliasToBean(Role.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            roleList =query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return roleList;
    }

    public List<Personnel> findPersonnelList(int offset, int length){
        List<Personnel> personnelList = null;
        try {
            String sql="SELECT * FROM personnel";
            SQLQuery query = getCurrentSession().createSQLQuery(sql);
            query.setResultTransformer(Transformers.aliasToBean(Personnel.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            personnelList =query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return personnelList;
    }
    public void updateUser(int id){
        getCurrentSession().createSQLQuery("UPDATE user SET user.addressId=0 WHERE user.id="+id+"");

    }

    public int getUserAllRowCount(String date,String source,String level,String userName) {
        String sql="select u.userName,u.startDate,m.level,u.name,u.gender,u.phone,u.email,u.orderNum,u.source ";

        String exceptSelectSql="from User u\n" +
                "LEFT JOIN member AS m ON m.userId = u.id\n" +
                "WHERE u.deletes =0";
        if (level !=null & level!=""){
            exceptSelectSql+=" AND m.level='"+level+"'";
        }
        if (source != null & source != ""){
            exceptSelectSql+=" AND u.source ='"+source+"'";
        }

        if (date !=null & date!="") {
            if (Integer.valueOf(date) == 1) {
                exceptSelectSql += " ORDER BY u.startDate";

            } else if (Integer.valueOf(date) == 0) {
                exceptSelectSql += " ORDER BY u.startDate DESC ";

            }
        }

        if (userName!=null & userName!=""){
            exceptSelectSql+=" AND u.name LIKE '%"+userName+"%'";
        }
        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }


    public int getRoleAllRowCount() {
        String sql="select * FROM role";
        return getCurrentSession().createSQLQuery(sql).list().size();
    }

    public int getPersonnelAllRowCount() {
        String sql="select * FROM personnel";
        return getCurrentSession().createSQLQuery(sql).list().size();
    }

    public int getRecycleAllRowCount(String date,String source,String level,String userName) {
        String sql="select u.userName,u.startDate,m.level,u.name,u.gender,u.phone,u.email,u.orderNum,u.source ";
        String exceptSelectSql="from User u\n" +
                "LEFT JOIN member AS m ON m.userId = u.id\n" +
                "WHERE u.deletes =1";
        if (level !=null & level!=""){
            exceptSelectSql+=" AND m.level='"+level+"'";
        }
        if (source != null & source != ""){
            exceptSelectSql+=" AND u.source ='"+source+"'";
        }
        if (date !=null & date!="") {
            if (Integer.valueOf(date) == 1) {
                exceptSelectSql += " ORDER BY u.startDate";

            } else if (Integer.valueOf(date) == 0) {
                exceptSelectSql += " ORDER BY u.startDate DESC ";

            }
        }

        if (userName!=null & userName!=""){
            exceptSelectSql+=" AND u.name LIKE '%"+userName+"%'";
        }
        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public int getIntegralAllRowCount(String date,String integral,String level,String userName) {
        /*String sql ="select m.memberCode,u.userName,m.startDate,m.level,m.integral from Member m,User u where m.userId = u.id and u.delete =0";*/
        String sql="select m.id,m.memberCode,u.userName,m.startDate,m.level,m.integral";
        String exceptSelectSql=" from Member m  \n" +
                " LEFT JOIN User as u on u.id = m.userId \n" +
                " WHERE u.deletes=0";

        if (date !=null & date!="") {
            if (Integer.valueOf(date) == 1) {
                exceptSelectSql += " ORDER BY u.startDate";

            } else if (Integer.valueOf(date) == 0) {
                exceptSelectSql += " ORDER BY u.startDate DESC ";

            }
        }

        if (integral!=null & integral!=""){
            if (Integer.valueOf(integral)==1){
                exceptSelectSql += " ORDER BY m.integral";
            }else if(Integer.valueOf(integral)==0){
                exceptSelectSql+=" ORDER BY m.integral DESC ";
            }
        }
        if (level !=null & level!=""){
            exceptSelectSql+=" AND m.level='"+level+"'";
        }

        if (userName!=null & userName!=""){
            exceptSelectSql+=" AND u.userName LIKE '%"+userName+"%'";
        }
        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public void deleteUsers(String ids){
        String sql ="UPDATE user u SET u.deletes = 1 WHERE u.id IN ("+ids+")";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }
    public void recycleUser(String ids){
        String sql ="UPDATE user u SET u.deletes = 0 WHERE u.id IN ("+ids+")";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public void deleteIntegral(String ids){
        String sql ="UPDATE member m SET m.integral = 0 WHERE m.id IN ("+ids+")";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public List<UserMember> recycleUserList(int offset,String date,String source,String level,String userName, int length){
        List<UserMember> userMemberList = null;
        try {
            String sql="select u.id,u.userName,u.startDate,m.level,u.name,u.gender,u.phone,u.email,u.orderNum,u.source ";
            String exceptSelectSql="from User u\n" +
                    "LEFT JOIN member AS m ON m.userId = u.id\n" +
                    "WHERE u.deletes = 1";
            if (level !=null & level!=""){
                exceptSelectSql+=" AND m.level='"+level+"'";
            }
            if (source != null & source != ""){
                exceptSelectSql+=" AND u.source ='"+source+"'";
            }
            if (date !=null & date!="") {
                if (Integer.valueOf(date) == 1) {
                    exceptSelectSql += " ORDER BY u.startDate";

                } else if (Integer.valueOf(date) == 0) {
                    exceptSelectSql += " ORDER BY u.startDate DESC ";

                }
            }
            if (userName!=null & userName!=""){
                exceptSelectSql+=" AND u.name LIKE '%"+userName+"%'";
            }
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id", StandardBasicTypes.INTEGER);
            query.addScalar("userName", StandardBasicTypes.STRING);
            query.addScalar("startDate", StandardBasicTypes.DATE);
            query.addScalar("level", StandardBasicTypes.STRING);
            query.addScalar("name", StandardBasicTypes.STRING);
            query.addScalar("gender", StandardBasicTypes.STRING);
            query.addScalar("phone", StandardBasicTypes.STRING);
            query.addScalar("email", StandardBasicTypes.STRING);
            query.addScalar("orderNum", StandardBasicTypes.LONG);
            query.addScalar("source", StandardBasicTypes.STRING);
            query.setResultTransformer(Transformers.aliasToBean(UserMember.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            userMemberList =query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return userMemberList;
    }

    public List<MemberUser> IntegralManagementList(int offset,String date,String integral,String level,String userName, int length){
        List<MemberUser> memberUserList = null;
        try {
           /* String sql ="select m.id,m.memberCode,u.userName,m.startDate,m.level,m.integral from Member m,User u where m.userId = u.id and u.delete =0";*/
            String sql="select m.id,m.memberCode,u.userName,m.startDate,m.level,m.integral";
            String exceptSelectSql=" from Member m " +
                    " LEFT JOIN User as u on m.userId = u.id " +
                    " WHERE u.deletes=0";
            if (date !=null & date!="") {
                if (Integer.valueOf(date) == 1) {
                    exceptSelectSql += " ORDER BY u.startDate";

                } else if (Integer.valueOf(date) == 0) {
                    exceptSelectSql += " ORDER BY u.startDate DESC ";

                }
            }

            if (integral!=null & integral!=""){

                if (Integer.valueOf(integral)==1){
                    exceptSelectSql += " ORDER BY m.integral";
                }else if(Integer.valueOf(integral)==0){
                    exceptSelectSql+=" ORDER BY m.integral DESC ";
                }
            }
            if (level !=null & level!=""){
                exceptSelectSql+=" AND m.level='"+level+"'";
            }

            if (userName!=null & userName!=""){
                exceptSelectSql+=" AND u.userName LIKE '%"+userName+"%'";
            }
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id",StandardBasicTypes.INTEGER);
            query.addScalar("memberCode",StandardBasicTypes.STRING);
            query.addScalar("userName",StandardBasicTypes.STRING);
            query.addScalar("startDate",StandardBasicTypes.DATE);
            query.addScalar("level",StandardBasicTypes.STRING);
            query.addScalar("integral",StandardBasicTypes.LONG);
            query.setResultTransformer(Transformers.aliasToBean(MemberUser.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            memberUserList = query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return memberUserList;
    }


    public UserMember findUserMemberById(int id){
        List<UserMember> userMemberList = null;
        String sql="select u.id,u.userName,u.startDate,m.level,u.name,u.gender,u.phone,u.email,u.orderNum,u.source ";
        String exceptSelectSql=" from User u\n" +
                " LEFT JOIN member AS m ON m.userId = u.id\n" +
                " WHERE u.id="+id+"";
        SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
        query.addScalar("id", StandardBasicTypes.INTEGER);
        query.addScalar("userName", StandardBasicTypes.STRING);
        query.addScalar("startDate", StandardBasicTypes.DATE);
        query.addScalar("level", StandardBasicTypes.STRING);
        query.addScalar("name", StandardBasicTypes.STRING);
        query.addScalar("gender", StandardBasicTypes.STRING);
        query.addScalar("phone", StandardBasicTypes.STRING);
        query.addScalar("email", StandardBasicTypes.STRING);
        query.addScalar("orderNum", StandardBasicTypes.LONG);
        query.addScalar("source", StandardBasicTypes.STRING);
        query.setResultTransformer(Transformers.aliasToBean(UserMember.class));
        userMemberList =query.list();
        return userMemberList.get(0);
    }

    public void updateUserM(int id,String userName,String name,String gender,String phone,String email){
        String sql="UPDATE user u SET u.userName='"+userName+"' ,u.name='"+name+"',u.gender='"+gender+"',u.phone='"+phone+"',u.email='"+email+"' WHERE u.id ="+id+"";
        getCurrentSession().createSQLQuery(sql).executeUpdate();

    }
    
    public void updateUserPwd(User user){
        String sql="UPDATE user u SET u.password='"+user.getPassword()+"' WHERE u.phone = '"+user.getPhone()+"'";
        getCurrentSession().createSQLQuery(sql).executeUpdate();

    }

    public List<Personnel> findPersonnel(String userName,String password){
        String sql="SELECT * FROM Personnel p WHERE p.userName='"+userName+"' AND p.password ='"+password+"'";
        List<Personnel> personnelList = getCurrentSession().createSQLQuery(sql).list();
        return  personnelList;
    }

    public List<Personnel> findPersonnelByName(String userName){
        String sql="SELECT * FROM Personnel p WHERE  p.userName='"+userName+"'";
        List<Personnel> personnelList = getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(Personnel.class)).list();
        return personnelList;
    }

    public Role findRole(int id){
        String sql ="select * from role r where r.id ="+id+"";
        List<Role> roleList = getCurrentSession().createSQLQuery(sql).addEntity(Role.class).list();
        return roleList.get(0);
    }

    public List<Menu> menuList(String ids){
        String sql="SELECT * FROM menu m WHERE  m.id in("+ids+")";
        List<Menu> menuList = getCurrentSession().createSQLQuery(sql).addEntity(Menu.class).list();
        return menuList;
    }

    public void updateRole(int id,String productListIds,String userListIds,String orderListIds,String systemListIds){
        String sql="UPDATE role r SET r.productList='"+productListIds+"',r.userList='"+userListIds+"',r.orderList='"+orderListIds+"',r.systemList='"+systemListIds+"' WHERE r.id="+id+"";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public List<Role> findRoleList(){
        return getCurrentSession().createSQLQuery("SELECT * FROM role").addEntity(Role.class).list();
    }

    public void updatePersonnel(Personnel personnel,String password){
        getCurrentSession().createSQLQuery("UPDATE personnel p SET p.password='"+password+"' WHERE p.id="+personnel.getId()+"").executeUpdate();
    }

    public  List<Personnel> findPersonnelList(){
        return getCurrentSession().createSQLQuery("SELECT  * FROM personnel").addEntity(Personnel.class).list();
    }

    public void addRole(Role role){
        String sql="INSERT INTO role(role.roleName,role.describe) VALUES ('"+role.getRoleName()+"','"+role.getDescribe()+"')";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public void addPersonnel(Personnel personnel){
        getCurrentSession().save(personnel);
    }

    public void deletePersonnel(int id){
        String sql="DELETE FROM personnel WHERE personnel.id="+id+"";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public List<Personnel> findPersonnelListById(int id){
        String sql="SELECT * FROM personnel p WHERE  p.roleId="+id+"";
        List<Personnel> personnelList= getCurrentSession().createSQLQuery(sql).addEntity(Personnel.class).list();
        return personnelList;
    }

    public void deleteRole(int id){
        String sql="DELETE FROM role WHERE role.id="+id+"";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public List<Advertisement> findAdvertisementList(){
        String sql="SELECT * FROM advertisement";
        List<Advertisement> advertisementList = getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(Advertisement.class)).list();
        return advertisementList;
    }

    public void addAdvertisement(Advertisement advertisement){
        getCurrentSession().save(advertisement);
    }

    public void updateAdvertisement(int id,Advertisement advertisement){
        String sql=null;
        if (advertisement.getPicture()!=null) {
             sql = "UPDATE advertisement a SET a.picture = '" + advertisement.getPicture() + "',a.adName='" + advertisement.getAdName() + "',a.url='" + advertisement.getUrl() + "'WHERE a.id=" + id + "";
        }else {
             sql = "UPDATE advertisement a SET a.adName='" + advertisement.getAdName() + "',a.url='" + advertisement.getUrl() + "'WHERE a.id=" + id + "";
        }
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public List<Advertisement> findAdById(int id){
        String sql ="SELECT * FROM advertisement a WHERE a.id="+id+"";
        List<Advertisement> advertisementList =getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(Advertisement.class)).list();
        return advertisementList;
    }

    public void deleteAdvertisement(int id){
        getCurrentSession().createSQLQuery("DELETE FROM advertisement WHERE advertisement.id="+id+"").executeUpdate();
    }

    public List<User> findUserByPhone(String phone){
        String sql="SELECT * FROM user WHERE user.phone='"+phone+"'";
        List<User> userList = getCurrentSession().createSQLQuery(sql).addEntity(User.class).list();
        return userList;
    }

    public void updateDefault(int userId,int addressId){
        getCurrentSession().createSQLQuery("UPDATE USER u SET u.addressId="+addressId+" WHERE u.id="+userId+"").executeUpdate();
    }

    public User findUserById(int id){
        String sql ="SELECT * FROM user WHERE user.id="+id+"";
        List<User> userList =getCurrentSession().createSQLQuery(sql).addEntity(User.class).list();
        return userList.get(0);
    }

    public List<User> findUserByPhonePd(String phone,String password){
        String sql="SELECT * FROM user WHERE user.phone='"+phone+"' AND user.password='"+password+"'";
        List<User> userList = getCurrentSession().createSQLQuery(sql).list();
        return userList;
    }

    public void deleteAddresses(int id){
        getCurrentSession().createSQLQuery("UPDATE address SET address.deletes=1 WHERE address.id="+id+"").executeUpdate();
    }

    public void updateUser(String path,String name,String sex,int id){
        getCurrentSession().createSQLQuery("UPDATE user SET user.userPicture='"+path+"',user.name='"+name+"',user.gender='"+sex+"' WHERE user.id="+id+"").executeUpdate();

    }
}
